package lesson14;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Test01 {
    /** 数据库连接字符串 */
    private String url = "jdbc:mysql://localhost:3306/j183?" +
            "userSSL=true&useUnicode=true&" +
            "characterEncoding=UTF8&serverTimezone=GMT";
    private String user = "root";
    private String pwd = "lovo";
    /** 数据库连接接口 */
    private Connection con;
    /** 执行SQL语句的对象 */
    private PreparedStatement statement;
    /** 结果集对象 */
    private ResultSet rs;
    /**
     * 获取数据库连接
     */
    public void getConnection(){
        con = null;
        try {
            // 1. 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2. 获取连接
            con = DriverManager.getConnection(url,user,pwd);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        System.out.println("数据库连接：" + con);
    }

    /**
     * 关闭所有连接
     */
    public void close(){
        // 关闭连接
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(statement != null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(con != null){
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    public int addStudent(Student student){
        int i = 0;
        try{
            // 打开连接
            this.getConnection();
            // 创建执行SQL的预编译对象
            this.statement = this.con.prepareStatement("insert into student(name,age,gender) values(?,?,?)");
            this.statement.setString(1,student.getName());
            this.statement.setInt(2,student.getAge());
            this.statement.setString(3,student.getGender());
            // 执行SQL
            i = this.statement.executeUpdate();
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            // 关闭连接
            this.close();
        }
        return i;

    }
    public int updateStudent(Student student){
        int i = 0;
        try{
            this.getConnection();
            this.statement = this.con.prepareStatement("update student set name=?,gender=?,age=? where id=?");
            this.statement.setString(1,student.getName());
            this.statement.setString(2,student.getGender());
            this.statement.setInt(3,student.getAge());
            this.statement.setInt(4,student.getId());
            i = this.statement.executeUpdate();
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            this.close();
        }
        return i;
    }
    public int deleteStudent(int id){
        int i = 0;
        try{
            this.getConnection();
            this.statement = this.con.prepareStatement("delete from student where id=?");
            this.statement.setInt(1,id);
            i = this.statement.executeUpdate();

        }catch(Exception e){
            e.printStackTrace();
        }finally {
            this.close();
        }
        return i;
    }

    /**
     * 查询所有学生
     * @return
     */
    public List<Student> find(){
        List<Student> list = new ArrayList<>();
        try{
            this.getConnection();
            statement = this.con.prepareStatement("select * from student");
            this.rs = this.statement.executeQuery();
            while(rs.next()){
                Student student = new Student();
                student.setId(rs.getInt(1));
                student.setName(rs.getString("name"));
                student.setGender(rs.getString("gender"));
                student.setAge(rs.getInt("age"));
                list.add(student);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            this.close();
        }
        return list;
    }
    public List<Student> findByName(String name){

        List<Student> list = new ArrayList<>();
        try{
            this.getConnection();
            statement = this.con.prepareStatement("select * from student where name=?");
            this.statement.setString(1,name);
            this.rs = this.statement.executeQuery();
            while(rs.next()){
                Student student = new Student();
                student.setId(rs.getInt(1));
                student.setName(rs.getString("name"));
                student.setGender(rs.getString("gender"));
                student.setAge(rs.getInt("age"));
                list.add(student);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            this.close();
        }
        return list;
    }
    public static void main(String[] args) {
        Test01 test01 = new Test01();
//        test01.getConnection();
//        test01.close();
//        System.out.println(test01.addStudent(new Student("张三","女",25)));
//        System.out.println(test01.updateStudent(new Student(15,"李四","男",25)));
//        System.out.println(test01.deleteStudent(15));

//        System.out.println(test01.find());
        System.out.println(test01.findByName("张飞"));
    }

}
